﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
//using Oracle.DataAccess.Client;
using System.Configuration;
//using System.Data.Entity.Infrastructure;
using System.ComponentModel.DataAnnotations;
using DslamPort.Models;
using System.Collections.ObjectModel;
using System.Web.Mvc;
using System.Data.OracleClient;
using System.Data;

namespace DslamPort.Models
{
    //[Authorize(Roles = "admin")]
    public class ReportInternetModel
    {
        public string id { get; set; }
        public string system_id { get; set; }
        public string name { get; set; }
        public string ip { get; set; }
        public string type_name { get; set; }
        public string sum_port_run { get; set; }
        public string sum_port_stop { get; set; }
        public string sum_port_lock { get; set; }
        public string ten_don_vi { get; set; }
        public string quan_huyen_id { get; set; }
        public List<ReportInternetModel> getDslamPort(string str = "", string id = "")
        {
            List<ReportInternetModel> DslamPort = new List<ReportInternetModel>();
            string sql = "";
            if (str != "")
            {
                sql += "select quan_huyen_id,ten_don_vi,system_id,name,ip,type_name,decode(sum(column0),null,0,sum(column0)) hoat_dong,decode(sum(column1),null,0,sum(column1)) tam_ngung,decode(sum(column2),null,0,sum(column2)) khoa ";
                sql += " from( SELECT a.*,b.name,c.quan_huyen_id quan_huyen_id,c.ten ten_don_vi,b.ip,d.id type_name,decode(a.trang_thai_int,'1',1) column0,  decode(a.trang_thai_int,'0',1) column1,  decode(a.trang_thai_int,'2',1) column2";
                sql += " FROM dslam_port a,dslam b, quan_huyen c, dslam_type d where d.id=b.dslam_type_id and a.system_id=b.system_id and b.quan_huyen_id=c.quan_huyen_id  " + str + ") group by quan_huyen_id,ten_don_vi,system_id,name,ip,type_name order by ten_don_vi,system_id,name";
                
            }
            else
                sql = "select * from quan_huyen where quan_huyen_id=0";
            var odr = BaseClass.Lib_GetDataReader(sql);
            foreach (DataRow item in odr.Rows)
            {
                DslamPort.Add(new ReportInternetModel
                {
                    quan_huyen_id = item["quan_huyen_id"].ToString(),
                    ten_don_vi = item["ten_don_vi"].ToString(),
                    system_id = item["system_id"].ToString(),
                    name = item["name"].ToString(),
                    ip = item["ip"].ToString(),
                    type_name = item["type_name"].ToString(),
                    sum_port_run = item["hoat_dong"].ToString(),
                    sum_port_lock = item["khoa"].ToString(),
                    sum_port_stop = item["tam_ngung"].ToString()
                });

            }
            
            return DslamPort;
        }


    }


}
